package com.aceleeyy.controller;

import cn.hutool.core.util.IdUtil;
import cn.hutool.json.JSONUtil;
import com.aceleeyy.action.DataBaseQuery;
import com.aceleeyy.action.DataBaseQueryContext;
import com.aceleeyy.common.constant.AceleeyyCommonInfo;
import com.aceleeyy.common.constant.Result;
import com.aceleeyy.common.error.ErrorMessage;
import com.aceleeyy.common.web.controller.BaseController;
import com.aceleeyy.common.web.model.DBInfoVo;
import com.aceleeyy.common.web.model.DBSQLVo;
import com.aceleeyy.common.web.model.ExtendSource;
import com.google.common.base.Throwables;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * 数据库连接
 *
 * @Author Ace Lee
 * @Date 2019/9/4 9:29
 * @Version 1.0
 **/
@Api(value = "数据库",description = "数据库")
@Slf4j
@RestController
@RequestMapping("/dbs")
public class DBController extends BaseController {

    @Autowired
    private DataBaseQueryContext dataBaseQueryContext;
    @Autowired
    private RedisTemplate redisTemplate;


    @ApiOperation(value = "执行SQL")
    @PostMapping("/execute/{id}")
    public Result execute(HttpServletRequest request,@PathVariable String id, @RequestBody DBSQLVo params) {
        log.info("执行SQL，请求参数：{},{}",id,params);
        Result result;

        //判断此连接信息是否存在
        String key = AceleeyyCommonInfo.REDIS_KEY_USER_DB+":"+id;
        Object o = request.getSession().getAttribute(key);
        if (null==o){
            result = Result.error(ErrorMessage.E_009.getErrDesc());
            return result;
        }

        //获取连接
        String value = (String) o;
        ExtendSource extendSource = JSONUtil.toBean(value, ExtendSource.class);
        DataBaseQuery dataBaseQuery = dataBaseQueryContext.build(extendSource.getDbType());
        if (null==dataBaseQuery){
            result = Result.error(ErrorMessage.E_004.getErrDesc());
            return result;
        }

        //判断SQL的增删改查
        String sql = params.getSql();
        if (StringUtils.isEmpty(sql)){
            result = Result.error(ErrorMessage.E_001.getErrDesc());
            return result;
        }
        String sqlPrefix = sql.trim().substring(0,6);
        //增删改
        if (sqlPrefix.equalsIgnoreCase(AceleeyyCommonInfo.SQL_TYPE_INSERT)
                || sqlPrefix.equalsIgnoreCase(AceleeyyCommonInfo.SQL_TYPE_DELETE)
                || sqlPrefix.equalsIgnoreCase(AceleeyyCommonInfo.SQL_TYPE_UPDATE) ){
            Optional<Integer> optionalDML = dataBaseQuery.executeDML(extendSource, sql);
            log.info("执行SQL，[增删改]结果：{}",optionalDML);
            Integer integer = null;
            if(optionalDML.isPresent()){
                integer = optionalDML.get();
            }
            result = new Result<>(integer);
        }else {
            Optional<List<Map<String, Object>>> optionalQ = dataBaseQuery.query(extendSource, sql);
            log.info("执行SQL，[查]结果：{}",optionalQ);
            List<Map<String, Object>> list = null;
            if (optionalQ.isPresent()){
                list = optionalQ.get();
            }
            result = new Result<>(list);
        }
        return result;
    }


    /**
     * 数据库连接
     *      如果连接成功，返回数据库元数据信息
     *      连接成功，将连接信息放入缓存，执行SQL的连接信息从缓存取出
     *      后台有定时清理长时间不适用的数据库连接
     *      退出连接时，清理缓存
     *
     * @param request
     * @param params 数据库连接信息
     * @return 数据库表和字段信息
     */
    @ApiOperation(value = "连接")
    @PostMapping("/conn")
    public Result conn(HttpServletRequest request, @RequestBody DBInfoVo params){
        log.info("数据库连接，请求参数：{}",params);
        Result result;
        Map<String,Object> map = Maps.newHashMap();
        List<Map<String,Object>> resDatas = Lists.newArrayList();

        try {
            //1.连接数据库信息
            String userName = params.getUsername();
            String password = params.getPassword();
            String jdbcUrl = params.getUrl();
            String dbType = params.getType();
            String schema = params.getSchema();
            if (StringUtils.isEmpty(jdbcUrl) || StringUtils.isEmpty(dbType)){
                return Result.error(ErrorMessage.E_001.getErrDesc());
            }

            ExtendSource extendSource = new ExtendSource();
            extendSource.setConnectionInfo("{\"userName\":\""+userName+"\",\"password\":\""+password+"\",\"jdbcUrl\":\""+jdbcUrl+"\"}");
            extendSource.setDbType(dbType);
            //2.给这次的连接一个唯一标识
            String id = IdUtil.randomUUID();
            map.put("id",id);
            extendSource.setId(id);

            //3.将extendSource对象存入session
            String key = AceleeyyCommonInfo.REDIS_KEY_USER_DB+":"+id;
            String value = JSONUtil.toJsonStr(extendSource);
            HttpSession session = request.getSession(AceleeyyCommonInfo.BOOLEAN_YES);
            session.setAttribute(key,value);
            session.setMaxInactiveInterval(AceleeyyCommonInfo.REDIS_KEY_USER_DB_TIME);//有效期(s)

            DataBaseQuery dataBaseQuery = dataBaseQueryContext.build(extendSource.getDbType());

            //4.根据数据库类型查询数据库元数据
            switch (dbType){
                case AceleeyyCommonInfo.DB_TYPE_DM:
                    getSchemasForDm(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                case AceleeyyCommonInfo.DB_TYPE_MYSQL5:
                    getSchemaForMysql5(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                case AceleeyyCommonInfo.DB_TYPE_ORACLE:
                    getSchemaForOracle(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                case AceleeyyCommonInfo.DB_TYPE_SQLITE:
                    getSchemaForSqlite(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                case AceleeyyCommonInfo.DB_TYPE_SQLSERVER:
                    getSchemaForSqlserver(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                case AceleeyyCommonInfo.DB_TYPE_DB2:
                    getSchemaForDb2(resDatas, schema, extendSource, dataBaseQuery);
                    break;
                default:
                    return Result.error(ErrorMessage.E_004.getErrDesc());
            }
            map.put("schemas",resDatas);
            result=new Result<>(map);
        } catch (Exception e) {
            log.error("连接异常：{}", Throwables.getStackTraceAsString(e));
            throw new RuntimeException(ErrorMessage.E_999.getErrDesc());
        }
        return result;
    }

    private void getSchemaForDb2(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "select CREATOR as OWNER, NAME as TABLE_NAME, TYPE as TABLE_TYPE, REMARKS as COMMENTS FROM SYSIBM.SYSTABLES " +
                "WHERE (TYPE = 'T' or  TYPE = 'V') AND CREATOR = '"+schema+"' order by TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[DB2]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "select NAME as COLUMN_NAME,COLTYPE as DATA_TYPE,LENGTH as DATA_LENGTH,REMARKS as COMMENTS from SYSIBM.SYSCOLUMNS " +
                            "where TBCREATOR = '"+schema+"' and TBNAME='"+table.get("TABLE_NAME")+"'";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[DB2]表字段结果：{}",optionalC);
                setColumns(resDatas, optionalC, tables);
            }
        }
    }

    private void getSchemaForSqlserver(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "select '"+schema+"' as OWNER,t1.name as TABLE_NAME,t1.xtype as TABLE_TYPE,t2.value as COMMENTS from sysobjects t1 " +
                "left join sys.extended_properties t2 on(t1.id=t2.major_id) where xtype='u' or xtype='v' order by TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[SQL Server]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "select t2.name as COLUMN_NAME, t3.name as DATA_TYPE, t2.max_length as DATA_LENGTH, t4.value as COMMENTS " +
                            "from sys.tables t1 INNER JOIN sys.columns t2 ON t2.object_id = t1.object_id INNER JOIN sys.types t3 ON t3.system_type_id = t2.system_type_id " +
                            "LEFT JOIN sys.extended_properties t4 ON t4.major_id = t2.object_id AND t4.minor_id = t2.column_id WHERE t1.name = '"+table.get("TABLE_NAME")+"'";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[SQL Server]表字段结果：{}",optionalC);
                setColumns(resDatas, optionalC, tables);
            }
        }
    }

    private void getSchemaForSqlite(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "select '"+schema+"' as OWNER,name as TABLE_NAME,type as TABLE_TYPE,'' as COMMENTS from sqlite_master order by TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[Sqlite]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "PRAGMA table_info('"+table.get("TABLE_NAME")+"');";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[Sqlite]表字段结果：{}",optionalC);
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    if (optionalC.isPresent()) {
                        Map<String, Object> tCloumns = optionalC.get();
                        if (!tCloumns.isEmpty() && null!=tCloumns.get(table.get("TABLE_NAME"))) {
                            List<Map<String,Object>> columnInfos = (List<Map<String, Object>>) tCloumns.get(table.get("TABLE_NAME"));

                            List<Map<String,Object>> cols = Lists.newArrayList();
                            if (!CollectionUtils.isEmpty(columnInfos)){
                                Map<String,Object> column = null;
                                for (int j = 0; j < columnInfos.size(); j++) {
                                    column = Maps.newHashMap();
                                    column.put("COLUMN_NAME",columnInfos.get(j).get("name"));
                                    Object typeO = columnInfos.get(j).get("type");
                                    if (null!=typeO){
                                        String type = (String) typeO;
                                        if (type.length()>0 && type.indexOf("(")>0 && type.indexOf(")")>0){
                                            column.put("DATA_TYPE",type.substring(0,type.indexOf("(")));
                                            column.put("DATA_LENGTH",type.substring(type.indexOf("(")+1,type.indexOf(")")));
                                        }else {
                                            column.put("DATA_TYPE",type);
                                            column.put("DATA_LENGTH",0);
                                        }
                                    }
                                    column.put("COMMENTS","");
                                    cols.add(column);
                                }
                            }
                            table.put("TABLE_COLUMNS", cols);
                        }
                    }
                    resDatas.add(table);
                }
            }
        }
    }

    private void getSchemaForOracle(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "select OWNER,TABLE_NAME,TABLE_TYPE,COMMENTS from all_tab_comments where OWNER='"+schema+"' order by TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[Oracle]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "select t1.COLUMN_NAME,t1.DATA_TYPE,t1.DATA_LENGTH,t2.COMMENTS from user_tab_columns t1 left join user_col_comments t2 on(t1.TABLE_NAME=t2.TABLE_NAME and t1.COLUMN_NAME=t2.COLUMN_NAME) " +
                            "where t1.table_name='"+table.get("TABLE_NAME")+"'";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[Oracle]表字段结果：{}",optionalC);
                setColumns(resDatas, optionalC, tables);
            }
        }
    }

    private void getSchemaForMysql5(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "SELECT TABLE_SCHEMA as OWNER,TABLE_NAME,TABLE_TYPE,TABLE_COMMENT as COMMENTS  FROM information_schema. TABLES WHERE table_schema = '"+schema+"' ORDER BY TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[MySQL5]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "select COLUMN_NAME,DATA_TYPE,COLUMN_TYPE as DATA_LENGTH,COLUMN_COMMENT as COMMENTS from information_schema.columns  where table_schema = '"+schema+"'  and table_name = '"+
                            table.get("TABLE_NAME")+"'";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[MySQL5]表字段结果：{}",optionalC);
                setColumns(resDatas, optionalC, tables);
            }
        }
    }

    private void setColumns(List<Map<String, Object>> resDatas, Optional<Map<String, Object>> optionalC, List<Map<String, Object>> tables) {
        Map<String, Object> table;
        for (int i = 0; i < tables.size(); i++) {
            table = tables.get(i);
            if (optionalC.isPresent()) {
                Map<String, Object> tCloumns = optionalC.get();
                if (!tCloumns.isEmpty()) {
                    table.put("TABLE_COLUMNS", tCloumns.get(table.get("TABLE_NAME")));
                }
            }
            resDatas.add(table);
        }
    }

    private void getSchemasForDm(List<Map<String, Object>> resDatas, String schema, ExtendSource extendSource, DataBaseQuery dataBaseQuery) {
        String sql = null;
        Optional<List<Map<String, Object>>> optionalT = null;
        Optional<Map<String, Object>> optionalC = null;

        sql = "select OWNER,TABLE_NAME,TABLE_TYPE,COMMENTS from  all_tab_comments where owner='"+schema+"' order  by TABLE_NAME asc";
        optionalT = dataBaseQuery.query(extendSource,sql);
        log.info("数据库连接，查询[达梦]表结果：{}",optionalT);
        if(optionalT.isPresent()){
            List<Map<String,Object>> tables = optionalT.get();
            if (!CollectionUtils.isEmpty(tables)){
                Map<String,String> sqlMap = Maps.newHashMap();
                Map<String,Object> table;
                for (int i = 0; i < tables.size(); i++) {
                    table = tables.get(i);
                    sql = "SELECT   T1.COLUMN_NAME,T1.DATA_TYPE,T1.DATA_LENGTH,T2.COMMENTS FROM ALL_TAB_COLUMNS T1  LEFT JOIN ALL_COL_COMMENTS T2 ON  (T1.TABLE_NAME= T2.TABLE_NAME AND  T1.COLUMN_NAME= T2.COLUMN_NAME) " +
                            "WHERE T1.TABLE_NAME= '"+table.get("TABLE_NAME")+"' AND T1.OWNER='"+schema+"'  AND T2.OWNER='"+schema+"' ";

                    sqlMap.put((String) table.get("TABLE_NAME"),sql);
                }

                optionalC = dataBaseQuery.querys(extendSource,sqlMap);
                log.info("数据库连接，查询[达梦]表字段结果：{}",optionalC);
                setColumns(resDatas, optionalC, tables);
            }
        }
    }
}
